﻿using CK.Sprite.Form.Core;
using CK.Sprite.Framework;
using CK.Sprite.ThirdContract;
using Dapper;
using Dapper.Contrib.Extensions;
using JetBrains.Annotations;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace CK.Sprite.Form.MySql
{
    public class MysqlRuntimeRepository : BaseSpriteRepository<Object>, IRuntimeRepository
    {
        public MysqlRuntimeRepository(IUnitOfWork unitOfWork) : base(unitOfWork) { }

        #region Sql

        private const string SqlDefaultCreate = "INSERT INTO `#TableName#`(#Fields#) VALUES (#Values#);";
        private const string SqlDefaultUpdate = "UPDATE `#TableName#` SET #FieldValues# WHERE Id=@Id #SqlWhere#;";
        private const string SqlDefaultDelete = "DELETE FROM `#TableName#` WHERE Id=@Id #SqlWhere#;";
        private const string SqlDefaultGet = "SELECT #Fields# FROM `#TableName#` WHERE Id=@Id #SqlWhere#;";
        private const string SqlDefaultList = "SELECT #Fields# FROM `#TableName#` WHERE 1=1 #SqlWhere##OrderBy#;";

        private const string SqlDefaultUpdateWhere = "UPDATE `#TableName#` SET #FieldValues# WHERE #SqlWhere#;";
        private const string SqlDefaultDeleteWhere = "DELETE FROM `#TableName#` WHERE #SqlWhere#;";
        private const string SqlDefaultGetWhere = "SELECT #Fields# FROM `#TableName#` WHERE #SqlWhere#;";
        private const string SqlDefaultListWhere = "SELECT #Fields# FROM `#TableName#` WHERE #SqlWhere##OrderBy#;";

        private const string SqlDefaultPageCount = "SELECT COUNT(1) FROM `#TableName#` WHERE #SqlWhere#";
        private const string SqlDefaultPageList = "SELECT #Fields# FROM `#TableName#` WHERE #SqlWhere##OrderBy# LIMIT #SkipCount#,#MaxResultCount#;";

        private const string SqlUniqCreate = "SELECT 1 FROM `#TableName#` WHERE #Field#=@#Field##SqlWhere# LIMIT 1;";
        private const string SqlUniqUpdate = "SELECT 1 FROM `#TableName#` WHERE #Field#=@#Field##SqlWhere# AND Id<>@Id LIMIT 1;";

        #endregion

        #region Default Method Call

        public async Task<JObject> DoDefaultCreateMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, string sqlMethodContent = "")
        {
            StringBuilder sbInsertFields = new StringBuilder();
            StringBuilder sbInsertValues = new StringBuilder();

            var newGuidId = Guid.NewGuid();
            if (paramValues.ContainsKey("id") && !string.IsNullOrEmpty(paramValues["id"].ToString()))
            {
                if (spriteObjectDto.KeyType == EKeyType.Guid)
                {
                    newGuidId = Guid.Parse(paramValues["id"].ToString());
                }
                sbInsertFields.Append($"{MysqlConsts.PreMark}Id{MysqlConsts.PostMark},");
                sbInsertValues.Append($"@id,");
            }
            else
            {
                if (spriteObjectDto.KeyType == EKeyType.Guid)
                {
                    sbInsertFields.Append($"{MysqlConsts.PreMark}Id{MysqlConsts.PostMark},");
                    sbInsertValues.Append($"'{newGuidId}',");
                }
                else
                {
                    sbInsertFields.Append($"{MysqlConsts.PreMark}Id{MysqlConsts.PostMark},");
                    sbInsertValues.Append($"0,");
                }
            }


            List<string> addedFields = new List<string>();
            foreach (var paramValue in paramValues)
            {
                var field = paramValue.Key;
                var findProperty = spriteObjectDto.ObjectPropertyDtos.FirstOrDefault(r => r.Name.ToLower() == field.ToLower());
                if (findProperty != null)
                {
                    addedFields.Add(findProperty.Name);
                    if (findProperty.FieldType != EFieldType.String && findProperty.FieldType != EFieldType.Text)
                    {
                        if (string.IsNullOrEmpty(paramValue.Value.ToString()))
                        {
                            paramValues[field] = null;
                        }
                    }
                    sbInsertFields.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark},");
                    sbInsertValues.Append($"@{field},");
                }
            }

            if(spriteObjectDto.IsWorkflow)
            {
                var workflowFields = new List<string>() { "InstanceId" , "FlowStartTime" , "FlowStartUserId" };
                foreach (var paramValue in paramValues)
                {
                    var field = paramValue.Key;
                    var findWorkflowField = workflowFields.FirstOrDefault(r => r.ToLower() == field.ToLower());
                    if (!string.IsNullOrEmpty(findWorkflowField))
                    {
                        addedFields.Add(findWorkflowField);
                        sbInsertFields.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark},");
                        sbInsertValues.Append($"@{field},");
                    }
                }
            }

            var tempParamValues = paramValues.DeepClone().ToObject<JObject>();
            var nowTime = DateTime.Now;

            var otherProperties = spriteObjectDto.ObjectPropertyDtos.Where(r => !addedFields.Any(t => t.ToLower() == r.Name.ToLower())).ToList();
            foreach (var otherProperty in otherProperties)
            {
                if (!otherProperty.IsNull)
                {
                    switch (otherProperty.FieldType)
                    {
                        case EFieldType.Text:
                        case EFieldType.String:
                            sbInsertFields.Append($"{MysqlConsts.PreMark}{otherProperty.Name}{MysqlConsts.PostMark},");
                            sbInsertValues.Append($"@{otherProperty.Name},");
                            tempParamValues.Add(new JProperty(otherProperty.Name, ""));
                            break;
                        case EFieldType.AutoNumber:
                        case EFieldType.Decimal:
                        case EFieldType.Float:
                        case EFieldType.Bool:
                        case EFieldType.Int:
                            sbInsertFields.Append($"{MysqlConsts.PreMark}{otherProperty.Name}{MysqlConsts.PostMark},");
                            sbInsertValues.Append($"@{otherProperty.Name},");
                            tempParamValues.Add(new JProperty(otherProperty.Name, 0));
                            break;
                        case EFieldType.DateTime:
                        case EFieldType.Date:
                            sbInsertFields.Append($"{MysqlConsts.PreMark}{otherProperty.Name}{MysqlConsts.PostMark},");
                            sbInsertValues.Append($"@{otherProperty.Name},");
                            tempParamValues.Add(new JProperty(otherProperty.Name, DateTime.MinValue));
                            break;
                        case EFieldType.AutoGuid:
                            sbInsertFields.Append($"{MysqlConsts.PreMark}{otherProperty.Name}{MysqlConsts.PostMark},");
                            sbInsertValues.Append($"@{otherProperty.Name},");
                            tempParamValues.Add(new JProperty(otherProperty.Name, Guid.NewGuid()));
                            break;
                        case EFieldType.Guid:
                            sbInsertFields.Append($"{MysqlConsts.PreMark}{otherProperty.Name}{MysqlConsts.PostMark},");
                            sbInsertValues.Append($"@{otherProperty.Name},");
                            tempParamValues.Add(new JProperty(otherProperty.Name, default(Guid)));
                            break;
                        default:
                            sbInsertFields.Append($"{MysqlConsts.PreMark}{otherProperty.Name}{MysqlConsts.PostMark},");
                            sbInsertValues.Append($"@{otherProperty.Name},");
                            tempParamValues.Add(new JProperty(otherProperty.Name, ""));
                            break;
                    }
                }
            }

            if (spriteObjectDto.IsTree)
            {
                CreateTree(sbInsertFields, sbInsertValues, spriteObjectDto, tempParamValues);
            }

            if (spriteObjectDto.CreateAudit)
            {
                CreateAuditCreate(sbInsertFields, sbInsertValues, nowTime, tempParamValues);
            }

            if (spriteObjectDto.ModifyAudit)
            {
                CreateAuditUpdate(sbInsertFields, sbInsertValues, nowTime, tempParamValues);
            }

            await CheckSameAsync(spriteObjectDto, paramValues, true);

            var strInserSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultCreate : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#Fields#", sbInsertFields.ToString().TrimEnd(','))
                .Replace("#Values#", sbInsertValues.ToString().TrimEnd(','));

            JObject result = new JObject();
            if (spriteObjectDto.KeyType == EKeyType.Guid)
            {
                await _unitOfWork.Connection.ExecuteAsync(strInserSql, tempParamValues.ToConventionalDotNetObject());
                result.Add(new JProperty("result", newGuidId));
            }
            else
            {
                var resultId = await _unitOfWork.Connection.QueryFirstAsync<int>(strInserSql + "SELECT LAST_INSERT_ID();", tempParamValues.ToConventionalDotNetObject());
                result.Add(new JProperty("result", resultId));
            }

            return result;
        }

        public async Task<JObject> DoDefaultUpdateMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, string sqlMethodContent = "")
        {
            StringBuilder sbUpdateFieldValues = new StringBuilder();

            foreach (var paramValue in paramValues)
            {
                var field = paramValue.Key;
                if (field != "Id" && spriteObjectDto.ObjectPropertyDtos.Any(r => r.Name.ToLower() == field.ToLower()))
                {
                    sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}=@{field},");
                }

                var findProperty = spriteObjectDto.ObjectPropertyDtos.FirstOrDefault(r => r.Name.ToLower() == field.ToLower());
                if (findProperty != null)
                {
                    if (findProperty.FieldType != EFieldType.String && findProperty.FieldType != EFieldType.Text)
                    {
                        if (string.IsNullOrEmpty(paramValue.Value.ToString()))
                        {
                            paramValues[field] = null;
                        }
                    }
                }
            }

            var tempParamValues = paramValues.DeepClone().ToObject<JObject>();
            var nowTime = DateTime.Now;

            if (spriteObjectDto.IsTree)
            {
                UpdateTree(sbUpdateFieldValues, spriteObjectDto, tempParamValues);
            }

            if (spriteObjectDto.ModifyAudit)
            {
                UpdateAuditUpdate(sbUpdateFieldValues, nowTime, tempParamValues);
            }

            await CheckSameAsync(spriteObjectDto, paramValues, false);

            var strUpdateSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultUpdate : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#FieldValues#", sbUpdateFieldValues.ToString().TrimEnd(','))
                .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));


            JObject result = new JObject();
            var resultRow = await _unitOfWork.Connection.ExecuteAsync(strUpdateSql, tempParamValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", paramValues["id"]));

            return result;
        }

        public async Task<JObject> DoDefaultDeleteMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, string sqlMethodContent = "")
        {
            JObject result = new JObject();
            var tempParamValues = paramValues.DeepClone().ToObject<JObject>();
            if (spriteObjectDto.DeleteAudit)
            {
                StringBuilder sbUpdateFieldValues = new StringBuilder();
                var nowTime = DateTime.Now;
                DeleteAuditDelete(sbUpdateFieldValues, nowTime, tempParamValues);

                var strUpdateSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultUpdate : sqlMethodContent)
                    .Replace("#TableName#", spriteObjectDto.Name)
                    .Replace("#FieldValues#", sbUpdateFieldValues.ToString().TrimEnd(','))
                    .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));

                var resultRow = await _unitOfWork.Connection.ExecuteAsync(strUpdateSql, tempParamValues.ToConventionalDotNetObject());
                result.Add(new JProperty("result", resultRow));
            }
            else
            {
                var strDeleteSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultDelete : sqlMethodContent).Replace("#TableName#", spriteObjectDto.Name).Replace("#SqlWhere#", "");
                var resultRow = await _unitOfWork.Connection.ExecuteAsync(strDeleteSql, tempParamValues.ToConventionalDotNetObject());
                result.Add(new JProperty("result", resultRow));
            }

            return result;
        }

        public async Task<JObject> DoDefaultGetMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JArray fields, string sqlMethodContent = "")
        {
            JObject result = new JObject();

            var resultGet = await _unitOfWork.Connection.QueryFirstOrDefaultAsync<dynamic>((string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultGet : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                .Replace("#Fields#", CreateSqlFields(spriteObjectDto, fields)), paramValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", JObject.FromObject(resultGet, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));

            return result;
        }

        public async Task<JObject> DoDefaultListMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JArray fields, JToken orderbys, string sqlMethodContent = "")
        {
            JObject result = new JObject();

            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            if (orderbys != null)
            {
                CommonConsts.CheckSqlInject(orderbys.ToString());
            }
            var resultList = await _unitOfWork.Connection.QueryAsync<dynamic>((string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultList : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                .Replace("#Fields#", CreateSqlFields(spriteObjectDto, fields))
                .Replace("#OrderBy#", orderbys == null ? "" : $" ORDER BY {orderbys.ToString()}"), paramValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", JArray.FromObject(resultList, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));

            return result;
        }

        public async Task<JObject> DoDefaultUpdateWhereMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JToken sqlWheres, string sqlMethodContent = "")
        {
            StringBuilder sbUpdateFieldValues = new StringBuilder();

            foreach (var paramValue in paramValues)
            {
                var field = paramValue.Key;
                if (field != "Id" && spriteObjectDto.ObjectPropertyDtos.Any(r => r.Name.ToLower() == field.ToLower()))
                {
                    sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}=@{field},");
                }
            }

            var tempParamValues = paramValues.DeepClone().ToObject<JObject>();
            var nowTime = DateTime.Now;

            //if (spriteObjectDto.IsTree) // ?不支持树批量修改
            //{
            //    UpdateTree(sbUpdateFieldValues, spriteObjectDto, tempParamValues);
            //}

            if (spriteObjectDto.ModifyAudit)
            {
                UpdateAuditUpdate(sbUpdateFieldValues, nowTime, tempParamValues);
            }

            string strSqlWhere = CreateSqlWhere(sqlWheres, tempParamValues);

            var strUpdateSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultUpdateWhere : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#FieldValues#", sbUpdateFieldValues.ToString().TrimEnd(','))
                .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));


            JObject result = new JObject();
            var resultRow = await _unitOfWork.Connection.ExecuteAsync(strUpdateSql, tempParamValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", resultRow));

            return result;
        }

        public async Task<JObject> DoDefaultDeleteWhereMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JToken sqlWheres, string sqlMethodContent = "")
        {
            JObject result = new JObject();
            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            string strSqlWhere = CreateSqlWhere(sqlWheres, paramValues);

            if (spriteObjectDto.DeleteAudit)
            {
                StringBuilder sbUpdateFieldValues = new StringBuilder();
                var nowTime = DateTime.Now;

                DeleteAuditDelete(sbUpdateFieldValues, nowTime, paramValues);

                var strUpdateSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultUpdateWhere : sqlMethodContent)
                    .Replace("#TableName#", spriteObjectDto.Name)
                    .Replace("#FieldValues#", sbUpdateFieldValues.ToString().TrimEnd(','))
                    .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));

                var resultRow = await _unitOfWork.Connection.ExecuteAsync(strUpdateSql, paramValues.ToConventionalDotNetObject());
                result.Add(new JProperty("result", resultRow));
            }
            else
            {
                string strDeleteSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultDeleteWhere : sqlMethodContent)
                    .Replace("#TableName#", spriteObjectDto.Name)
                    .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));
                var resultRow = await _unitOfWork.Connection.ExecuteAsync(strDeleteSql, paramValues.ToConventionalDotNetObject());
                result.Add(new JProperty("result", resultRow));
            }

            return result;
        }

        public async Task<JObject> DoDefaultGetWhereMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JToken sqlWheres, JArray fields, string sqlMethodContent = "")
        {
            JObject result = new JObject();

            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            string strSqlWhere = CreateSqlWhere(sqlWheres, paramValues);
            var strGetSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultGetWhere : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                .Replace("#Fields#", CreateSqlFields(spriteObjectDto, fields));

            var resultGet = await _unitOfWork.Connection.QueryFirstOrDefaultAsync<dynamic>(strGetSql, paramValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", JObject.FromObject(resultGet, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));

            return result;
        }

        public async Task<JObject> DoDefaultListWhereMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JToken sqlWheres, JArray fields, JToken orderbys, string sqlMethodContent = "")
        {
            JObject result = new JObject();

            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            if (orderbys != null)
            {
                CommonConsts.CheckSqlInject(orderbys.ToString());
            }
            string strSqlWhere = CreateSqlWhere(sqlWheres, paramValues);

            var strListSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultListWhere : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                .Replace("#Fields#", CreateSqlFields(spriteObjectDto, fields))
                .Replace("#OrderBy#", orderbys == null ? "" : $" ORDER BY {orderbys.ToString()}");
            var resultList = await _unitOfWork.Connection.QueryAsync<dynamic>(strListSql, paramValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", JArray.FromObject(resultList, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));

            return result;
        }

        public async Task<JObject> DoMultiListWhereMethodAsync(Dictionary<string, SpriteObjectDto> dictAliasInfos, JToken joinInfos, JObject paramValues, JToken sqlWheres, JArray fields, JToken orderbys, string sqlMethodContent = "")
        {
            StringBuilder sbDeleteAudit = new StringBuilder();
            List<string> strOriginFields = new List<string>();

            GetMultiInfos(dictAliasInfos, sbDeleteAudit, strOriginFields);

            JObject result = new JObject();

            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            if (joinInfos != null)
            {
                CommonConsts.CheckSqlInject(joinInfos.ToString());
            }
            if (orderbys != null)
            {
                CommonConsts.CheckSqlInject(orderbys.ToString());
            }
            string strSqlWhere = CreateSqlWhere(sqlWheres, paramValues);

            var strSql = $"SELECT #Fields# FROM (SELECT {string.Join(",", strOriginFields)} FROM {joinInfos.ToString()}) t WHERE #SqlWhere##OrderBy#;";

            var strListSql = strSql
                .Replace("#SqlWhere#", strSqlWhere + sbDeleteAudit.ToString())
                .Replace("#Fields#", CreateSqlFields(null, fields))
                .Replace("#OrderBy#", orderbys == null ? "" : $" ORDER BY {orderbys.ToString()}");

            var resultList = await _unitOfWork.Connection.QueryAsync<dynamic>(strListSql, paramValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", JArray.FromObject(resultList, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));

            return result;
        }

        public async Task<JObject> DoSqlMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JToken sqlWheres, JArray fields, JToken orderbys, string sqlMethodContent)
        {
            JObject result = new JObject();

            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            if (orderbys != null)
            {
                CommonConsts.CheckSqlInject(orderbys.ToString());
            }
            string strSqlWhere = CreateSqlWhere(sqlWheres, paramValues);

            var strListSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultListWhere : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", strSqlWhere)
                .Replace("#Fields#", CreateSqlFields(spriteObjectDto, fields))
                .Replace("#OrderBy#", orderbys == null ? "" : $" ORDER BY {orderbys.ToString()}");
            var resultList = await _unitOfWork.Connection.QueryAsync<dynamic>(strListSql, paramValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", JArray.FromObject(resultList, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));

            return result;
        }

        public async Task<JObject> DoDefaultPageListMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JToken sqlWheres, JArray fields, JToken orderbys, JToken maxResultCount, JToken skipCount, string sqlMethodContent = "")
        {
            JObject result = new JObject();

            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            if (orderbys != null)
            {
                CommonConsts.CheckSqlInject(orderbys.ToString());
            }
            string strSqlWhere = CreateSqlWhere(sqlWheres, paramValues);

            var intMaxResultCount = int.MaxValue;
            var intSkipCount = 0;
            if (maxResultCount != null)
            {
                intMaxResultCount = maxResultCount.ToObject<int>();
            }
            if (skipCount != null)
            {
                intSkipCount = skipCount.ToObject<int>();
            }

            var strPageListSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultPageList : sqlMethodContent.Split(';')[0])
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                .Replace("#Fields#", CreateSqlFields(spriteObjectDto, fields))
                .Replace("#OrderBy#", orderbys == null ? "" : $" ORDER BY {orderbys.ToString()}")
                .Replace("#SkipCount#", intSkipCount.ToString())
                .Replace("#MaxResultCount#", intMaxResultCount.ToString());
            var resultList = await _unitOfWork.Connection.QueryAsync<dynamic>(strPageListSql, paramValues.ToConventionalDotNetObject());

            var strCountSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultPageCount : sqlMethodContent.Split(';')[1])
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));

            var count = await _unitOfWork.Connection.QueryFirstAsync<int>(strCountSql, paramValues.ToConventionalDotNetObject());

            JObject jResut = new JObject();
            jResut.Add(new JProperty("items", JArray.FromObject(resultList, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));
            jResut.Add(new JProperty("count", count));

            result.Add(new JProperty("result", jResut));

            return result;
        }

        public async Task<JObject> DoMultiPageListMethodAsync(Dictionary<string, SpriteObjectDto> dictAliasInfos, JToken joinInfos, JObject paramValues, JToken sqlWheres, JArray fields, JToken orderbys, JToken maxResultCount, JToken skipCount, string sqlMethodContent = "")
        {
            // aliasInfos格式为：table1:a;table2:b，且与joinInfos需要完全对应
            //var strAliasInfos = aliasInfos.ToString();
            //var spliteAliasInfos = strAliasInfos.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
            //Dictionary<string, ObjectPropertyDto> dictAliasInfos = new Dictionary<string, ObjectPropertyDto>();
            //var spriteObjectLocalCache = ServiceLocator.ServiceProvider.GetService<SpriteObjectLocalCache>();
            //var allSpriteObjectDtos = spriteObjectLocalCache.GetAll();
            //foreach (var spliteAliasInfo in spliteAliasInfos)
            //{
            //    var tempSplitInfo = spliteAliasInfo.Split(':');
            //    //dictAliasInfos.Add(tempSplitInfo[1], )
            //}

            StringBuilder sbDeleteAudit = new StringBuilder();
            List<string> strOriginFields = new List<string>();

            GetMultiInfos(dictAliasInfos, sbDeleteAudit, strOriginFields);

            JObject result = new JObject();

            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            if (joinInfos != null)
            {
                CommonConsts.CheckSqlInject(joinInfos.ToString());
            }
            if (orderbys != null)
            {
                CommonConsts.CheckSqlInject(orderbys.ToString());
            }
            string strSqlWhere = CreateSqlWhere(sqlWheres, paramValues);

            var intMaxResultCount = int.MaxValue;
            var intSkipCount = 0;
            if (maxResultCount != null)
            {
                intMaxResultCount = maxResultCount.ToObject<int>();
            }
            if (skipCount != null)
            {
                intSkipCount = skipCount.ToObject<int>();
            }

            var strSql = $"SELECT #Fields# FROM (SELECT {string.Join(",", strOriginFields)} FROM {joinInfos.ToString()}) t WHERE #SqlWhere##OrderBy# LIMIT #SkipCount#,#MaxResultCount#;";
            var strCount = $"SELECT COUNT(1) FROM (SELECT {string.Join(",", strOriginFields)} FROM {joinInfos.ToString()}) t WHERE #SqlWhere#;";

            var strPageListSql = strSql
                    .Replace("#SqlWhere#", strSqlWhere + sbDeleteAudit.ToString())
                    .Replace("#Fields#", CreateSqlFields(null, fields))
                    .Replace("#OrderBy#", orderbys == null ? "" : $" ORDER BY {orderbys.ToString()}")
                    .Replace("#SkipCount#", intSkipCount.ToString())
                    .Replace("#MaxResultCount#", intMaxResultCount.ToString());
            var resultList = await _unitOfWork.Connection.QueryAsync<dynamic>(strPageListSql, paramValues.ToConventionalDotNetObject());

            var strCountSql = strCount
                .Replace("#SqlWhere#", strSqlWhere + sbDeleteAudit.ToString());

            var count = await _unitOfWork.Connection.QueryFirstAsync<int>(strCountSql, paramValues.ToConventionalDotNetObject());

            JObject jResut = new JObject();
            jResut.Add(new JProperty("items", JArray.FromObject(resultList, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));
            jResut.Add(new JProperty("count", count));

            result.Add(new JProperty("result", jResut));

            return result;
        }

        // 树和流程不支持批量新增
        public async Task<JObject> DoBatchCreateMethodAsync(SpriteObjectDto spriteObjectDto, JArray arrayParamValues)
        {
            bool isMultiIndex = false;
            int indexCount = 0;
            if (!string.IsNullOrEmpty(spriteObjectDto.UnionIndex))
            {
                indexCount++;
            }
            indexCount = spriteObjectDto.ObjectPropertyDtos.Count(r => r.IsUnique) + indexCount;
            if (indexCount > 1)
            {
                isMultiIndex = true;
            }

            JObject result = new JObject();
            if (arrayParamValues.Count == 0)
            {
                result.Add(new JProperty("result", 0));
                return result;
            }

            if (isMultiIndex)
            {
                foreach (JObject paramValues in arrayParamValues)
                {
                    await DoDefaultCreateMethodAsync(spriteObjectDto, paramValues);
                }
            }
            else
            {
                var nowTime = DateTime.Now;
                List<string> insertFields = new List<string>();
                List<string> insertValues = new List<string>();

                List<ObjectPropertyDto> otherProperties = new List<ObjectPropertyDto>();
                bool isFirst = true;
                foreach (JObject paramValues in arrayParamValues)
                {
                    StringBuilder sbTempValues = new StringBuilder();
                    sbTempValues.Append("(");

                    if (spriteObjectDto.KeyType == EKeyType.Guid)
                    {
                        if (isFirst)
                        {
                            insertFields.Add($"Id");
                        }
                        sbTempValues.Append($"'{Guid.NewGuid()}',");
                    }
                    else
                    {
                        if (isFirst)
                        {
                            insertFields.Add($"Id");
                        }
                        sbTempValues.Append($"'0',");
                    }

                    foreach (var objectPropertyDto in spriteObjectDto.ObjectPropertyDtos)
                    {
                        bool isNotFindParam = true;
                        if (paramValues.ContainsKey(objectPropertyDto.Name) || paramValues.ContainsKey(objectPropertyDto.Name.ToCamelCase())) // 找到传递的参数
                        {
                            var findParam = paramValues.ContainsKey(objectPropertyDto.Name) ? paramValues[objectPropertyDto.Name] : paramValues[objectPropertyDto.Name.ToCamelCase()];
                            if (findParam == null)
                            {
                                isNotFindParam = true;
                            }
                            else
                            {
                                isNotFindParam = false;
                                if (objectPropertyDto != null && objectPropertyDto.Name != "Id")
                                {
                                    if (isFirst)
                                    {
                                        insertFields.Add($"{MysqlConsts.PreMark}{objectPropertyDto.Name}{MysqlConsts.PostMark}");
                                    }
                                    if (string.IsNullOrEmpty(findParam.ToString()))
                                    {
                                        sbTempValues.Append($"NULL,");
                                    }
                                    else
                                    {
                                        if (objectPropertyDto.FieldType == EFieldType.Bool)
                                        {
                                            sbTempValues.Append(Convert.ToBoolean(findParam.ToString()) ? "1," : "0,");
                                        }
                                        else
                                        {
                                            if (objectPropertyDto.FieldType == EFieldType.Date || objectPropertyDto.FieldType == EFieldType.DateTime)
                                            {
                                                sbTempValues.Append($"'{findParam.ToObject<DateTime>().ToString("yyyy-MM-dd HH:mm:ss")}',");
                                            }
                                            else
                                            {
                                                sbTempValues.Append($"'{findParam.ToString()}',");
                                            }
                                        }
                                    }
                                }
                                    
                            }
                        }
                        if (isNotFindParam)
                        {
                            if (objectPropertyDto.IsNull)
                            {
                                if (isFirst)
                                {
                                    insertFields.Add($"{MysqlConsts.PreMark}{objectPropertyDto.Name}{MysqlConsts.PostMark}");
                                }
                                sbTempValues.Append($"NULL,");
                            }
                            else
                            {
                                switch (objectPropertyDto.FieldType)
                                {
                                    case EFieldType.Text:
                                    case EFieldType.String:
                                        if (isFirst)
                                        {
                                            insertFields.Add($"{MysqlConsts.PreMark}{objectPropertyDto.Name}{MysqlConsts.PostMark}");
                                        }
                                        sbTempValues.Append($"'',");
                                        break;
                                    case EFieldType.Decimal:
                                    case EFieldType.Float:
                                    case EFieldType.Bool:
                                    case EFieldType.Int:
                                        if (isFirst)
                                        {
                                            insertFields.Add($"{MysqlConsts.PreMark}{objectPropertyDto.Name}{MysqlConsts.PostMark}");
                                        }
                                        sbTempValues.Append($"'0',");
                                        break;
                                    case EFieldType.DateTime:
                                    case EFieldType.Date:
                                        if (isFirst)
                                        {
                                            insertFields.Add($"{MysqlConsts.PreMark}{objectPropertyDto.Name}{MysqlConsts.PostMark}");
                                        }
                                        sbTempValues.Append($"'{DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss")}',");
                                        break;
                                    case EFieldType.AutoNumber:
                                        if (isFirst)
                                        {
                                            insertFields.Add($"{MysqlConsts.PreMark}{objectPropertyDto.Name}{MysqlConsts.PostMark}");
                                        }
                                        sbTempValues.Append($"'0',");
                                        break;
                                    case EFieldType.AutoGuid:
                                        if (isFirst)
                                        {
                                            insertFields.Add($"{MysqlConsts.PreMark}{objectPropertyDto.Name}{MysqlConsts.PostMark}");
                                        }
                                        sbTempValues.Append($"'{Guid.NewGuid().ToString()}',");
                                        break;
                                    case EFieldType.Guid:
                                        if (isFirst)
                                        {
                                            insertFields.Add($"{MysqlConsts.PreMark}{objectPropertyDto.Name}{MysqlConsts.PostMark}");
                                        }
                                        sbTempValues.Append($"'{default(Guid).ToString()}',");
                                        break;
                                    default:
                                        if (isFirst)
                                        {
                                            insertFields.Add($"{MysqlConsts.PreMark}{objectPropertyDto.Name}{MysqlConsts.PostMark}");
                                        }
                                        sbTempValues.Append($"'',");
                                        break;
                                }
                            }
                        }
                    }

                    if (spriteObjectDto.CreateAudit)
                    {
                        if (isFirst)
                        {
                            insertFields.Add("CreatorId");
                        }
                        sbTempValues.Append($"'{ServiceLocator.ServiceProvider.GetService<ICurrentUser>().UserId}',");
                        if (isFirst)
                        {
                            insertFields.Add("CreationTime");
                        }
                        sbTempValues.Append($"'{nowTime.ToString("yyyy-MM-dd HH:mm:ss")}',");
                    }
                    if (spriteObjectDto.ModifyAudit)
                    {
                        if (isFirst)
                        {
                            insertFields.Add("LastModifierId");
                        }
                        sbTempValues.Append($"'{ServiceLocator.ServiceProvider.GetService<ICurrentUser>().UserId}',");
                        if (isFirst)
                        {
                            insertFields.Add("LastModificationTime");
                        }
                        sbTempValues.Append($"'{nowTime.ToString("yyyy-MM-dd HH:mm:ss")}',");
                    }

                    isFirst = false;

                    insertValues.Add(sbTempValues.ToString().TrimEnd(',') + ")");
                }

                var strSql = $"INSERT INTO `{spriteObjectDto.Name}`(#Fields#) VALUES ";
                strSql = strSql.Replace("#Fields#", string.Join(",", insertFields));
                strSql += string.Join(",", insertValues) + ";";

                await _unitOfWork.Connection.ExecuteAsync(strSql);
            }


            result.Add(new JProperty("result", 1));
            return result;
        }

        public async Task<JObject> DoBatchUpdateMethodAsync(SpriteObjectDto spriteObjectDto, JArray arrayParamValues)
        {
            JObject result = new JObject();
            if (arrayParamValues.Count == 0)
            {
                result.Add(new JProperty("result", 0));
                return result;
            }
            foreach (JObject paramValues in arrayParamValues)
            {
                await DoDefaultUpdateMethodAsync(spriteObjectDto, paramValues);
            }

            result.Add(new JProperty("result", 1));

            return result;
        }

        public async Task<JObject> DoGetUniqInfos(SpriteObjectDto spriteObjectDto, string uniqFieldInfo, List<string> uniqValues)
        {
            string strSql = "";

            if (spriteObjectDto.UnionIndex == uniqFieldInfo) // 联合索引
            {
                var splitUnionIndexs = spriteObjectDto.UnionIndex.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);

                List<string> strConcat = new List<string>();
                foreach (var splitUnionIndex in splitUnionIndexs)
                {
                    var findProperty = spriteObjectDto.ObjectPropertyDtos.FirstOrDefault(r => r.Name.ToLower() == splitUnionIndex.ToLower());
                    if (findProperty != null)
                    {
                        if (findProperty.FieldType == EFieldType.Date || findProperty.FieldType == EFieldType.DateTime)
                        {
                            strConcat.Add($"DATE_FORMAT({MysqlConsts.PreMark}{splitUnionIndex}{MysqlConsts.PostMark}, '%Y-%m-%d_%H:%i:%s')");
                            continue;
                        }
                        strConcat.Add($"{MysqlConsts.PreMark}{splitUnionIndex}{MysqlConsts.PostMark}");
                    }
                    else
                    {
                        throw new SpriteException($"联合索引字段[{splitUnionIndex}]未找到");
                    }
                }
                strSql = $"SELECT Id,CONCAT({string.Join(",'@#$',", strConcat)}) AS UniqData FROM {MysqlConsts.PreMark}{spriteObjectDto.Name}{MysqlConsts.PostMark} WHERE CONCAT({string.Join(",'@#$',", strConcat)}) IN ('{string.Join("','", uniqValues)}');";
            }
            else
            {
                var strField = uniqFieldInfo;
                var findProperty = spriteObjectDto.ObjectPropertyDtos.FirstOrDefault(r => r.Name.ToLower() == uniqFieldInfo.ToLower());
                if (findProperty != null)
                {
                    if (findProperty.FieldType == EFieldType.Date || findProperty.FieldType == EFieldType.DateTime)
                    {
                        strField = $"DATE_FORMAT({MysqlConsts.PreMark}{uniqFieldInfo}{MysqlConsts.PostMark}, '%Y-%m-%d_%H:%i:%s')";
                    }
                }
                strSql = $"SELECT Id,{MysqlConsts.PreMark}{strField}{MysqlConsts.PostMark} AS UniqData FROM {MysqlConsts.PreMark}{spriteObjectDto.Name}{MysqlConsts.PostMark} WHERE {MysqlConsts.PreMark}{strField}{MysqlConsts.PostMark} IN ('{string.Join("','", uniqValues)}');";
            }

            JObject result = new JObject();
            var resultList = await _unitOfWork.Connection.QueryAsync<dynamic>(strSql.Replace("#SqlWhere#", spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""), new { UniqValues = uniqValues.ToArray() });
            result.Add(new JProperty("result", JArray.FromObject(resultList)));

            return result;
        }

        public async Task<JArray> DoGetRemoteSelectCall(SpriteObjectDto spriteObjectDto, string filter, bool isAll)
        {
            var remoteSelectInfo = JsonConvert.DeserializeObject<RemoteSelectInfo>(spriteObjectDto.RemoteSelect);
            var strSql = $"SELECT #Fields# FROM {MysqlConsts.PreMark}{spriteObjectDto.Name}{MysqlConsts.PostMark}#SqlWhere##OrderBy# LIMIT #ResultCount#";
            if (string.IsNullOrEmpty(remoteSelectInfo.fields))
            {
                strSql = strSql.Replace("#Fields#", "*");
            }
            else
            {
                var splitFields = remoteSelectInfo.fields.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                List<string> listFields = new List<string>();
                foreach (var splitField in splitFields)
                {
                    listFields.Add($"{MysqlConsts.PreMark}{splitField}{MysqlConsts.PostMark}");
                }
                strSql = strSql.Replace("#Fields#", string.Join(",", listFields));
            }

            if (string.IsNullOrEmpty(remoteSelectInfo.whereFields) || string.IsNullOrEmpty(filter))
            {
                strSql = strSql.Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));
            }
            else
            {
                var splitWhereFields = remoteSelectInfo.whereFields.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                List<string> listWhereFields = new List<string>();
                foreach (var splitWhereField in splitWhereFields)
                {
                    listWhereFields.Add($"{MysqlConsts.PreMark}{splitWhereField}{MysqlConsts.PostMark} LIKE CONCAT('%',@Filter,'%')");
                }
                strSql = strSql.Replace("#SqlWhere#", $" WHERE {string.Join(" OR ", listWhereFields)}" + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));
            }
            if (string.IsNullOrEmpty(remoteSelectInfo.orderInfo))
            {
                strSql = strSql.Replace("#OrderBy#", "");
            }
            else
            {
                strSql = strSql.Replace("#OrderBy#", $" ORDER BY {remoteSelectInfo.orderInfo}");
            }

            if(isAll)
            {
                strSql = strSql.Replace(" LIMIT #ResultCount#", "");
            }
            else
            {
                if (remoteSelectInfo.resultCount > 0)
                {
                    strSql = strSql.Replace("#ResultCount#", $"{remoteSelectInfo.resultCount}");
                }
                else
                {
                    strSql = strSql.Replace("#ResultCount#", $"20");
                }
            }

            var resultList = await _unitOfWork.Connection.QueryAsync<dynamic>(strSql, new { Filter = filter });

            return JArray.FromObject(resultList, ExpressSqlHelper.CreateCamelCaseJsonSerializer());
        }

        public async Task<JArray> DoGetByIds(SpriteObjectDto spriteObjectDto, string ids)
        {
            if(string.IsNullOrEmpty(ids))
            {
                return new JArray();
            }
            List<string> listIds = ids.Split(new char[] { ';'}, StringSplitOptions.RemoveEmptyEntries).ToList();
            var strSql = $"SELECT * FROM {MysqlConsts.PreMark}{spriteObjectDto.Name}{MysqlConsts.PostMark} WHERE Id IN @Ids {(spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : "")};";
            var resultList = await _unitOfWork.Connection.QueryAsync<dynamic>(strSql, new { Ids = listIds.ToArray() });

            return JArray.FromObject(resultList, ExpressSqlHelper.CreateCamelCaseJsonSerializer());
        }

        #region 私有方法

        private string CreateSqlFields(SpriteObjectDto spriteObjectDto, JArray fields)
        {
            if (fields == null || fields.Count == 0)
            {
                return "*";
            }

            //var tempFields = fields.Where(r => spriteObjectDto.ObjectPropertyDtos.Any(t => t.Name == r.ToString())).ToList();
            //if (tempFields == null || tempFields.Count == 0)
            //{
            //    return "*";
            //}
            //fields = tempFields;

            CommonConsts.CheckSqlInject(fields.ToString());

            return string.Join(",", fields.Select(r => $"{MysqlConsts.PreMark}{r}{MysqlConsts.PostMark}"));
        }

        private string CreateSqlWhere(JToken sqlWheres, JObject tempParamValues)
        {
            if (sqlWheres == null)
            {
                return "1=1";
            }
            StringBuilder sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append("1=1");

            ExpressSqlModel expressSqlModel = sqlWheres.ToObject<ExpressSqlModel>();
            if (expressSqlModel != null && expressSqlModel.Children != null && expressSqlModel.Children.Count > 0)
            {
                string strSql = ExpressSqlHelper.CreateSqlWhere(expressSqlModel, tempParamValues, CreateConditionSql);
                if (!string.IsNullOrEmpty(strSql))
                {
                    sbSqlWhere.Append($" AND {strSql}");
                }
            }

            string strResult = sbSqlWhere.ToString();

            CommonConsts.CheckSqlInject(strResult, new List<string>() { "@"});
            return strResult;
        }

        private string CreateSqlWhereArray(JArray sqlWheres, JObject tempParamValues)
        {
            StringBuilder sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append("1=1");
            if (sqlWheres == null || sqlWheres.Count == 0)
            {
                return sbSqlWhere.ToString();
            }

            int index = 1;
            foreach (var sqlWhere in sqlWheres)
            {
                var conditionType = sqlWhere["conditionType"].ToObject<EConditionType>();
                var field = sqlWhere["field"].ToString();
                switch (conditionType)
                {
                    case EConditionType.等于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}=@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.Like:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} LIKE CONCAT('%',@SW{index}_{field},'%')");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.In:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IN @SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.Between:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}  BETWEEN @SW{index}_{field}_1 AND @SW{index}_{field}_2");
                        var inValues = sqlWhere["value"].ToArray();
                        tempParamValues.Add(new JProperty($"SW{index}_{field}_1", inValues[0].ToObject<object>()));
                        tempParamValues.Add(new JProperty($"SW{index}_{field}_2", inValues[1].ToObject<object>()));
                        break;
                    case EConditionType.大于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}>@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.大于等于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}>=@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.小于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.小于等于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<=@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.不等于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<>@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.Null:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IS NULL");
                        break;
                    case EConditionType.NotNull:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IS NOT NULL");
                        break;
                    case EConditionType.NotIn:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} NOT IN @SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    default:
                        break;
                }

                index++;
            }

            return sbSqlWhere.ToString();
        }

        private async Task CheckSameAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, bool isCreate = true)
        {
            if (!string.IsNullOrEmpty(spriteObjectDto.UnionIndex)) // 联合索引
            {
                var splitUnionIndexs = spriteObjectDto.UnionIndex.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);

                List<string> strConcat = new List<string>();
                List<string> strWhereValue = new List<string>();
                foreach (var splitUnionIndex in splitUnionIndexs)
                {
                    if (!paramValues.ContainsKey(splitUnionIndex.ToCamelCase()) && !paramValues.ContainsKey(splitUnionIndex))
                    {
                        throw new SpriteException($"联合索引字段[{splitUnionIndex}]未赋值");
                    }

                    var findProperty = spriteObjectDto.ObjectPropertyDtos.FirstOrDefault(r => r.Name.ToLower() == splitUnionIndex.ToLower());
                    if (findProperty != null)
                    {
                        var paramValue = paramValues.ContainsKey(splitUnionIndex.ToCamelCase()) ? paramValues[splitUnionIndex.ToCamelCase()] : paramValues.ContainsKey(splitUnionIndex);
                        if (findProperty.FieldType == EFieldType.Date || findProperty.FieldType == EFieldType.DateTime)
                        {
                            strConcat.Add($"DATE_FORMAT({MysqlConsts.PreMark}{splitUnionIndex}{MysqlConsts.PostMark}, '%Y-%m-%d_%H:%i:%s')");
                            strWhereValue.Add(paramValue.ToObject<DateTime>().ToString("yyyy-MM-dd_HH:mm:ss"));
                            continue;
                        }
                        strConcat.Add($"{MysqlConsts.PreMark}{splitUnionIndex}{MysqlConsts.PostMark}");
                        if (findProperty.FieldType == EFieldType.Bool)
                        {
                            strWhereValue.Add(paramValue.ToObject<bool>() ? "1" : "0");
                            continue;
                        }
                        strWhereValue.Add(paramValue.ToString());
                    }
                    else
                    {
                        List<string> otherProperties = new List<string>();
                        if (spriteObjectDto.IsTree)
                        {
                            otherProperties.AddRange(new List<string> { "PId", "TreeCode", "Path", "Title", "Icon" });
                        }
                        if (spriteObjectDto.CreateAudit)
                        {
                            otherProperties.AddRange(new List<string> { "CreatorId", "CreationTime" });
                        }
                        if (spriteObjectDto.ModifyAudit)
                        {
                            otherProperties.AddRange(new List<string> { "LastModifierId", "LastModificationTime" });
                        }
                        if (spriteObjectDto.DeleteAudit)
                        {
                            otherProperties.AddRange(new List<string> { "DeleterId", "DeletionTime", "IsDeleted" });
                        }
                        if (otherProperties.Count > 0)
                        {
                            if (otherProperties.Exists(r => r.ToLower() == splitUnionIndex.ToLower()))
                            {
                                var dateTimeFields = new List<string>() { "CreationTime", "LastModificationTime", "DeletionTime" };
                                var boolFields = new List<string>() { "IsDeleted" };
                                var paramValue = paramValues.ContainsKey(splitUnionIndex.ToCamelCase()) ? paramValues[splitUnionIndex.ToCamelCase()] : paramValues.ContainsKey(splitUnionIndex);
                                if (dateTimeFields.Exists(r => r.ToLower() == splitUnionIndex.ToLower()))
                                {
                                    strConcat.Add($"DATE_FORMAT({MysqlConsts.PreMark}{splitUnionIndex}{MysqlConsts.PostMark}, '%Y-%m-%d_%H:%i:%s')");
                                    strWhereValue.Add(paramValue.ToObject<DateTime>().ToString("yyyy-MM-dd_HH:mm:ss"));
                                    continue;
                                }
                                strConcat.Add($"{MysqlConsts.PreMark}{splitUnionIndex}{MysqlConsts.PostMark}");
                                if (boolFields.Exists(r => r.ToLower() == splitUnionIndex.ToLower()))
                                {
                                    strWhereValue.Add(paramValue.ToObject<bool>() ? "1" : "0");
                                    continue;
                                }
                                strWhereValue.Add(paramValue.ToString());
                            }
                            else
                            {
                                throw new SpriteException($"联合索引字段[{splitUnionIndex}]未找到");
                            }
                        }
                        else
                        {
                            throw new SpriteException($"联合索引字段[{splitUnionIndex}]未找到");
                        }
                    }
                }

                int? result = 0;
                if (isCreate)
                {
                    var strSql = $"SELECT 1 FROM {MysqlConsts.PreMark}{spriteObjectDto.Name}{MysqlConsts.PostMark} WHERE CONCAT({string.Join(",'@#$',", strConcat)})='{string.Join(",@#$,", strWhereValue)}'#SqlWhere# LIMIT 1;";
                    result = await _unitOfWork.Connection.QueryFirstOrDefaultAsync<int?>(strSql.Replace("#SqlWhere#", spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""), paramValues.ToConventionalDotNetObject());
                }
                else
                {
                    var strSql = $"SELECT 1 FROM {MysqlConsts.PreMark}{spriteObjectDto.Name}{MysqlConsts.PostMark} WHERE CONCAT({string.Join(",'@#$',", strConcat)})='{string.Join(",@#$,", strWhereValue)}' AND Id<>@Id#SqlWhere# LIMIT 1;";
                    result = await _unitOfWork.Connection.QueryFirstOrDefaultAsync<int?>(strSql.Replace("#SqlWhere#", spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""), paramValues.ToConventionalDotNetObject());
                }
                if (result == 1)
                {
                    throw new SpriteException($"联合索引【{spriteObjectDto.UnionIndex}】必须惟一，请检查！");
                }
            }

            var uniqFields = spriteObjectDto.ObjectPropertyDtos.Where(r => r.IsUnique).ToList();
            if (uniqFields.Count == 0)
            {
                return;
            }

            foreach (var uniqField in uniqFields)
            {
                int? result = 0;
                if (isCreate)
                {
                    result = await _unitOfWork.Connection.QueryFirstOrDefaultAsync<int?>(SqlUniqCreate.Replace("#TableName#", spriteObjectDto.Name)
                            .Replace("#Field#", uniqField.Name)
                            .Replace("#SqlWhere#", spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""), paramValues.ToConventionalDotNetObject());
                }
                else
                {
                    result = await _unitOfWork.Connection.QueryFirstOrDefaultAsync<int?>(SqlUniqUpdate.Replace("#TableName#", spriteObjectDto.Name)
                        .Replace("#Field#", uniqField.Name)
                        .Replace("#SqlWhere#", spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""), paramValues.ToConventionalDotNetObject());
                }
                if (result == 1)
                {
                    throw new SpriteException($"字段【{uniqField.Description}】必须惟一，请检查！");
                }
            }
        }

        private void GetMultiInfos(Dictionary<string, SpriteObjectDto> dictAliasInfos, StringBuilder sbDeleteAudit, List<string> strOriginFields)
        {
            Action<List<string>, string, string> getNewField = (parmAriginFields, alias, fieldName) =>
            {
                parmAriginFields.Add($"{alias}.{MysqlConsts.PreMark}{fieldName}{MysqlConsts.PostMark} AS {fieldName}_{alias}");
            };

            foreach (var dictAliasInfo in dictAliasInfos)
            {
                var spriteObjectDto = dictAliasInfo.Value;
                var alias = dictAliasInfo.Key;
                getNewField(strOriginFields, alias, "Id");
                foreach (var objProperty in spriteObjectDto.ObjectPropertyDtos)
                {
                    getNewField(strOriginFields, alias, objProperty.Name);
                }

                if (spriteObjectDto.IsTree)
                {
                    getNewField(strOriginFields, alias, "PId");
                    getNewField(strOriginFields, alias, "TreeCode");
                    getNewField(strOriginFields, alias, "Path");
                    getNewField(strOriginFields, alias, "Title");
                    getNewField(strOriginFields, alias, "Icon");
                }
                if (spriteObjectDto.CreateAudit)
                {
                    getNewField(strOriginFields, alias, "CreatorId");
                    getNewField(strOriginFields, alias, "CreationTime");
                }
                if (spriteObjectDto.IsWorkflow)
                {
                    getNewField(strOriginFields, alias, "InstanceId");
                    getNewField(strOriginFields, alias, "FlowStartTime");
                    getNewField(strOriginFields, alias, "FlowStartUserId");
                }
                if (spriteObjectDto.ModifyAudit)
                {
                    getNewField(strOriginFields, alias, "LastModifierId");
                    getNewField(strOriginFields, alias, "LastModificationTime");
                }
                if (spriteObjectDto.DeleteAudit)
                {
                    getNewField(strOriginFields, alias, "DeleterId");
                    getNewField(strOriginFields, alias, "DeletionTime");
                    getNewField(strOriginFields, alias, "IsDeleted");
                    sbDeleteAudit.Append($" AND IsDeleted_{alias}=0");
                }
            }
        }

        #endregion

        #region Auto Field Value

        private void CreateTree(StringBuilder sbInsertFields, StringBuilder sbInsertValues, SpriteObjectDto spriteObjectDto, JObject tempParamValues)
        {
            var pid = tempParamValues["pId"];
            sbInsertFields.Append($"{MysqlConsts.PreMark}PId{MysqlConsts.PostMark},");
            sbInsertFields.Append($"{MysqlConsts.PreMark}TreeCode{MysqlConsts.PostMark},");
            sbInsertFields.Append($"{MysqlConsts.PreMark}Path{MysqlConsts.PostMark},");
            sbInsertFields.Append($"{MysqlConsts.PreMark}Title{MysqlConsts.PostMark},");
            sbInsertFields.Append($"{MysqlConsts.PreMark}Icon{MysqlConsts.PostMark},");
            sbInsertValues.Append($"@PId,");
            sbInsertValues.Append($"@TreeCode,");
            sbInsertValues.Append($"@Path,");
            sbInsertValues.Append($"@Title,");
            sbInsertValues.Append($"@Icon,");
            if (pid == null || string.IsNullOrEmpty(pid.ToString()))
            {
                // PId,TreeCode,Path,Icon,Title
                object objPId;
                switch (spriteObjectDto.KeyType)
                {
                    case EKeyType.Int:
                        objPId = 0;
                        break;
                    case EKeyType.Guid:
                        objPId = Guid.Empty;
                        break;
                    default:
                        objPId = Guid.Empty;
                        break;
                }
                tempParamValues.Add(new JProperty("PId", objPId));
                tempParamValues.Remove("pId");
                tempParamValues.Add(new JProperty("TreeCode", "0."));
                tempParamValues.Remove("treeCode");
                tempParamValues.Add(new JProperty("Path", tempParamValues["title"]?.ToObject<object>()));
                tempParamValues.Remove("path");
                tempParamValues.Add(new JProperty("Title", tempParamValues["title"]?.ToObject<object>()));
                tempParamValues.Remove("title");
                tempParamValues.Add(new JProperty("Icon", tempParamValues["icon"]?.ToObject<object>()));
                tempParamValues.Remove("icon");
            }
            else
            {
                var resultGet = _unitOfWork.Connection.QueryFirstOrDefault<dynamic>(SqlDefaultGet
                    .Replace("#TableName#", spriteObjectDto.Name)
                    .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                    .Replace("#Fields#", CreateSqlFields(spriteObjectDto, null)), new { Id = pid?.ToObject<object>() });

                tempParamValues.Add(new JProperty("PId", resultGet.Id));
                tempParamValues.Remove("pId");
                tempParamValues.Add(new JProperty("TreeCode", $"{resultGet.TreeCode}{resultGet.Id}."));
                tempParamValues.Remove("treeCode");
                tempParamValues.Add(new JProperty("Path", $"{resultGet.Path}/{tempParamValues["title"]}"));
                tempParamValues.Remove("path");
                tempParamValues.Add(new JProperty("Title", tempParamValues["title"]?.ToObject<object>()));
                tempParamValues.Remove("title");
                tempParamValues.Add(new JProperty("Icon", tempParamValues["icon"]?.ToObject<object>()));
                tempParamValues.Remove("icon");
            }
        }

        private void CreateAuditCreate(StringBuilder sbInsertFields, StringBuilder sbInsertValues, DateTime nowTime, JObject tempParamValues)
        {
            sbInsertFields.Append($"{MysqlConsts.PreMark}CreatorId{MysqlConsts.PostMark},");
            sbInsertFields.Append($"{MysqlConsts.PreMark}CreationTime{MysqlConsts.PostMark},");
            sbInsertValues.Append($"@CreatorId,");
            sbInsertValues.Append($"@CreationTime,");

            tempParamValues.Add(new JProperty("CreatorId", ServiceLocator.ServiceProvider.GetService<ICurrentUser>().UserId));
            tempParamValues.Remove("creatorId");
            tempParamValues.Add(new JProperty("CreationTime", nowTime));
            tempParamValues.Remove("creationTime");
        }

        private void CreateAuditUpdate(StringBuilder sbInsertFields, StringBuilder sbInsertValues, DateTime nowTime, JObject tempParamValues)
        {
            sbInsertFields.Append($"{MysqlConsts.PreMark}LastModifierId{MysqlConsts.PostMark},");
            sbInsertFields.Append($"{MysqlConsts.PreMark}LastModificationTime{MysqlConsts.PostMark},");
            sbInsertValues.Append($"@LastModifierId,");
            sbInsertValues.Append($"@LastModificationTime,");

            tempParamValues.Add(new JProperty("LastModifierId", ServiceLocator.ServiceProvider.GetService<ICurrentUser>().UserId));
            tempParamValues.Remove("lastModifierId");
            tempParamValues.Add(new JProperty("LastModificationTime", nowTime));
            tempParamValues.Remove("lastModificationTime");
        }

        private void UpdateAuditUpdate(StringBuilder sbUpdateFieldValues, DateTime nowTime, JObject tempParamValues)
        {
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}LastModifierId{MysqlConsts.PostMark}=@LastModifierId,");
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}LastModificationTime{MysqlConsts.PostMark}=@LastModificationTime,");

            tempParamValues.Add(new JProperty("LastModifierId", ServiceLocator.ServiceProvider.GetService<ICurrentUser>().UserId));
            tempParamValues.Remove("lastModifierId");
            tempParamValues.Add(new JProperty("LastModificationTime", nowTime));
            tempParamValues.Remove("lastModificationTime");
        }

        private void UpdateTree(StringBuilder sbUpdateFieldValues, SpriteObjectDto spriteObjectDto, JObject tempParamValues)
        {
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}Title{MysqlConsts.PostMark}=@Title,");
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}Icon{MysqlConsts.PostMark}=@Icon,");
            tempParamValues.Add(new JProperty("Title", tempParamValues["title"]?.ToObject<object>()));
            tempParamValues.Remove("title");
            tempParamValues.Add(new JProperty("Icon", tempParamValues["icon"]?.ToObject<object>()));
            tempParamValues.Remove("icon");

            var pid = tempParamValues["pId"];
            if (pid == null || string.IsNullOrEmpty(pid.ToString()))
            {
                object objPId;
                switch (spriteObjectDto.KeyType)
                {
                    case EKeyType.Int:
                        objPId = 0;
                        break;
                    case EKeyType.Guid:
                        objPId = Guid.Empty;
                        break;
                    default:
                        objPId = Guid.Empty;
                        break;
                }

                sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}PId{MysqlConsts.PostMark}=@PId,");
                sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}TreeCode{MysqlConsts.PostMark}=@TreeCode,");
                sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}Path{MysqlConsts.PostMark}=@Path,");

                tempParamValues.Add(new JProperty("PId", objPId));
                tempParamValues.Remove("pId");
                tempParamValues.Add(new JProperty("TreeCode", "0."));
                tempParamValues.Remove("treeCode");
                tempParamValues.Add(new JProperty("Path", tempParamValues["title"].ToString()));
                tempParamValues.Remove("path");
                return;
            }

            var id = tempParamValues["id"];
            var dbData = _unitOfWork.Connection.QueryFirstOrDefault<dynamic>(SqlDefaultGet
                    .Replace("#TableName#", spriteObjectDto.Name)
                    .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                    .Replace("#Fields#", CreateSqlFields(spriteObjectDto, null)), new { Id = id?.ToObject<object>() });

            var dbParentData = _unitOfWork.Connection.QueryFirstOrDefault<dynamic>(SqlDefaultGet
                    .Replace("#TableName#", spriteObjectDto.Name)
                    .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                    .Replace("#Fields#", CreateSqlFields(spriteObjectDto, null)), new { Id = pid?.ToObject<object>() });

            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}Path{MysqlConsts.PostMark}=@Path,");
            tempParamValues.Add(new JProperty("Path", $"{dbParentData.Path}/{tempParamValues["title"]}"));
            tempParamValues.Remove("path");
            if (Convert.ToString(dbData.PId) == pid.ToString()) // 未修改PId
            {
                return;
            }

            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}PId{MysqlConsts.PostMark}=@PId,");
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}TreeCode{MysqlConsts.PostMark}=@TreeCode,");

            tempParamValues.Add(new JProperty("PId", dbParentData.Id));
            tempParamValues.Remove("pId");
            tempParamValues.Add(new JProperty("TreeCode", $"{dbParentData.TreeCode}{dbParentData.Id}."));
            tempParamValues.Remove("treeCode");
        }

        private void DeleteAuditDelete(StringBuilder sbUpdateFieldValues, DateTime nowTime, JObject tempParamValues)
        {
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}DeleterId{MysqlConsts.PostMark}=@DeleterId,");
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}DeletionTime{MysqlConsts.PostMark}=@DeletionTime,");
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}IsDeleted{MysqlConsts.PostMark}=@IsDeleted,");

            tempParamValues.Add(new JProperty("DeleterId", ServiceLocator.ServiceProvider.GetService<ICurrentUser>().UserId));
            tempParamValues.Remove("deleterId");
            tempParamValues.Add(new JProperty("DeletionTime", nowTime));
            tempParamValues.Remove("deletionTime");
            tempParamValues.Add(new JProperty("IsDeleted", true));
            tempParamValues.Remove("isDeleted");
        }

        #endregion

        #endregion
    }
}
